﻿using CommunityToolkit.Mvvm.Messaging;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;
using 畜牧业审核程序国产.Models.ToolsClass;

namespace 畜牧业审核程序国产.Models
{
    internal class Modify
    {
        public static string ModifyTables(IEnumerable<string> tables, IProgress<int> progress)
        {
            string 错误信息 = null;
            FuncOutCome modifyOutcome = new FuncOutCome();
            MyDBOperator modifyOperator = MyDBOperator.DBOperatorInstance;
            var tableList = tables.ToList();
            int totalFilesCount = tableList.Count;
            int processedFiles = 0;
            int percentage = 0;
            foreach (var table in tables)
            {
                using (DataTable dt = modifyOperator.GetDataTable($"select * from {table}"))
                {
                    string[] allColumnsNames = modifyOperator.GetColumnNamesArr(dt);
                    if (allColumnsNames.Contains("已修正"))
                    {
                        processedFiles++;
                        percentage = (int)((double)processedFiles / totalFilesCount * 100);
                        progress.Report(percentage);
                        continue;
                    }
                    else
                    {
                        string funcOutCome = ModifyCityCountryTownOfOneTable(table);
                        if (funcOutCome !=null)
                        {
                            processedFiles++;
                            percentage = (int)((double)processedFiles / totalFilesCount * 100);
                            progress.Report(percentage);
                            错误信息 += $"、{funcOutCome}";
                        }
                    }
                }


            }
            if (错误信息 ==null)
            {
                return 错误信息;
            }
            if (错误信息.Length > 1)
            {
                错误信息 = 错误信息.Substring(1);//把前面的顿号去掉
            }
            return 错误信息;
        }
        internal static string ModifyCityCountryTownOfOneTable(string withYearAndSeasonTableName)//对下面这个方法的替代
        {
            string outcome = null;
            //MyDBOperator modifyOperator = new MyDBOperator();改用单例模式
            MyDBOperator modifyOperator = MyDBOperator.DBOperatorInstance;
            try
            {
                #region 改市的
                string sqlForGetAllCity = $"select 市代码 from {withYearAndSeasonTableName}  group by 市代码";//从某张季度表里面找到有几个市代码
                DataTable dtOfCityCodeAndName = modifyOperator.GetDataTable(sqlForGetAllCity);
                if (dtOfCityCodeAndName == null|| dtOfCityCodeAndName.Rows.Count==0)//这种情况不太会出现,因为导入的时候全部设置了市代码\县代码\乡镇代码
                {
                    outcome = $"{withYearAndSeasonTableName}里面没有市代码或该表没有数据";
                    return outcome;
                }
                else//如果市代码存在
                {
                    List<string> strOfCityCodes_List = dtOfCityCodeAndName.AsEnumerable().
                        Select(r => r["市代码"].ToString())
                        .ToList();//获取市代码的的值(一般一个地级市就1个)

                    foreach (string itemCityCode in strOfCityCodes_List)//一般就1个
                    {
                        DataTable dtofRealNameOfitem = modifyOperator.GetDataTable($"select * from 全省区划代码 where 乡镇区划代码 ='{itemCityCode}'");
                        if (dtofRealNameOfitem == null || dtofRealNameOfitem.Rows.Count == 0)
                        {

                            outcome = ($"2022年的全省统计区划代码没有市代码为{itemCityCode}的市,请排查{withYearAndSeasonTableName}表中户编码以此开头的养殖户");
                            return outcome;
                        }
                        else
                        {
                            string CorrectNameOfCityCode = dtofRealNameOfitem.Rows[0][0].ToString();
                            modifyOperator.ExecuteBySql($"update {withYearAndSeasonTableName} set 市 = '{CorrectNameOfCityCode}' where 市代码 = '{itemCityCode}'");
                            
                         
                        }
                    }

                }
                #endregion

                #region 改乡镇的
                string SqlForGetAllTown = $"select 乡镇代码 from {withYearAndSeasonTableName} group by 乡镇代码";
                DataTable DTOfTownCodeAndName = modifyOperator.GetDataTable(SqlForGetAllTown);
                if (DTOfTownCodeAndName == null || DTOfTownCodeAndName.Rows.Count == 0)
                {
                    outcome = $"{withYearAndSeasonTableName}里面没有乡镇代码或该表没有数据";
                    return outcome;
                }
                else
                { 
                    //获取要导入的表里的所有不同乡镇的乡镇代码
                    List<string> ListStrOfTownCode = (from Row in DTOfTownCodeAndName.AsEnumerable()
                                                      select Row["乡镇代码"].ToString()).ToList();
                    string 省里没有的乡镇代码 = null;
                    foreach (string TownCodeItem in ListStrOfTownCode)
                    {
                        DataTable DtOFOneTownInProvince = modifyOperator.GetDataTable($"select * from 全省区划代码 where 乡镇区划代码 = '{TownCodeItem}'");
                        if (DtOFOneTownInProvince == null || DtOFOneTownInProvince.Rows.Count == 0)
                        {

                            outcome = $"2022年的全省统计区划代码没有乡镇代码为{TownCodeItem}的乡镇,请排查{withYearAndSeasonTableName}表中户编码以此开头的养殖户";
                            return outcome;
                        }
                        else
                        {
                            string CorrectTownName = DtOFOneTownInProvince.Rows[0][0].ToString();
                            modifyOperator.ExecuteBySql($"update {withYearAndSeasonTableName} set 乡= '{CorrectTownName}' where 乡镇代码  = '{TownCodeItem}' ");
                    
                          
                          
                        }
                    }
                }
               
                #endregion

                #region 改县的
                string sqlToGetAllCountry = $"select 县代码 from {withYearAndSeasonTableName} group by 县代码";
                DataTable DTOfCountryCodeAndName = modifyOperator.GetDataTable(sqlToGetAllCountry);
                if (DTOfCountryCodeAndName == null || DTOfCountryCodeAndName.Rows.Count == 0)
                {
                    outcome = $"{withYearAndSeasonTableName}里面没有县代码或该表没有数据";
                    return outcome;
                }
                else
                {
                    //获取的是要导入的表里的所有县代码的集合
                    List<string> ListStrOfCountryCode = DTOfCountryCodeAndName.AsEnumerable().Select(r => r["县代码"].ToString()).ToList();//从要修正的表中找到所有县代码,并组成集合

                    foreach (string CountryCodeItem in ListStrOfCountryCode)
                    {
                        DataTable DtOfOneCountryCodeAndNameInProvince = modifyOperator.GetDataTable($"select * from 全省区划代码 where 乡镇区划代码  = '{CountryCodeItem}'");
                        if (DtOfOneCountryCodeAndNameInProvince == null || DtOfOneCountryCodeAndNameInProvince.Rows.Count == 0)
                        {

                            outcome = $"2022年的全省统计区划代码没有县代码为{CountryCodeItem}的县区,请排查{withYearAndSeasonTableName}表中户编码以此开头的养殖户";
                            return outcome;
                        }
                        else
                        {
                            string CorrectCountryName = DtOfOneCountryCodeAndNameInProvince.Rows[0][0].ToString();//这里去掉了{CountryCodeitem}后面的三个0
                            modifyOperator.ExecuteBySql($"update {withYearAndSeasonTableName} set 县 = '{CorrectCountryName}' where 县代码 = '{CountryCodeItem}' ");
                           

                        }

                    }
                }
                #endregion

                DataTable dt = modifyOperator.GetDataTable($"select * from  {withYearAndSeasonTableName}");
                if (!modifyOperator.GetColumnNamesArr(dt).Contains("已修正"))
                {
                    string 是否修正标识 = $"ALTER TABLE {withYearAndSeasonTableName} ADD COLUMN 已修正 string";
                    modifyOperator.ExecuteBySql(是否修正标识);
                }
                
                return outcome;
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},{Environment.NewLine}方法为{ex.TargetSite}");
                outcome = $"错误:{ex.Message}";
                return outcome;
            }
        }




        public static List<FuncOutCome> ModityAll15Tables(string strYearNow, string strSeasonNow, string strYearPre, string strSeasonPre, string strYearYOY, string strSeasonYOY)//,IProgress<int> progress)
        {
            //int totalFiles = 15;
            //int processedFiles = 0;
            //int percentage = 0;
            List<FuncOutCome> modifyOutcome_List = new List<FuncOutCome>();
            //  MyDBOperator modifyOperator = new MyDBOperator();改用单例模式
            MyDBOperator modifyOperator = MyDBOperator.DBOperatorInstance;
            string[] allBeAuditTablesNamesA = new[] { "A209", "A210", "A211", "A212" };
            //这里要不要做个判断,如果有一套表,优先选择一套表,如果没有一套表,再使用统计云
            string platformNow = "一套表";
            string platformPre = "一套表";
            string platformYOY = "一套表";

            List<string> unexistsUnionTablesNow =
                modifyOperator.CheckTablesExist(new string[]{ "一套表" + strYearNow + strSeasonNow + "A209","一套表" + strYearNow + strSeasonNow + "A210",
                       "一套表" + strYearNow + strSeasonNow + "A211","一套表" + strYearNow + strSeasonNow + "A212"});
            if (unexistsUnionTablesNow.Count == 4)
            {
                platformNow = "统计云";
            }

            List<string> unexistsUnionTablesPre =
              modifyOperator.CheckTablesExist(new string[]{ "一套表" + strYearPre + strSeasonPre + "A209","一套表" + strYearPre + strSeasonPre + "A210",
                       "一套表" + strYearPre + strSeasonPre + "A211","一套表" + strYearPre + strSeasonPre + "A212"});
            if (unexistsUnionTablesPre.Count == 4)
            {
                platformPre = "统计云";
            }
            List<string> unexistsUnionTablesYOY =
              modifyOperator.CheckTablesExist(new string[]{ "一套表" + strYearYOY + strSeasonYOY + "A209","一套表" + strYearYOY + strSeasonYOY + "A210",
                       "一套表" + strYearYOY + strSeasonYOY + "A211","一套表" + strYearYOY + strSeasonYOY + "A212"});
            if (unexistsUnionTablesPre.Count == 4)
            {
                platformYOY = "统计云";
            }
            List<string> modifyTables = new List<string>();
            foreach (string tableNameA in allBeAuditTablesNamesA)
            {
                modifyTables.Add($"{platformNow}{strYearNow}{strSeasonNow}{tableNameA}");
            }
            foreach (string tableNameA in allBeAuditTablesNamesA)
            {
                modifyTables.Add($"{platformPre}{strYearPre}{strSeasonPre}{tableNameA}");
            }
            foreach (string tableNameA in allBeAuditTablesNamesA)
            {
                modifyTables.Add($"{platformYOY}{strYearYOY}{strSeasonYOY}{tableNameA}");
            }
            modifyTables.Add($"一套表{strYearNow}{strSeasonNow}A406");
            modifyTables.Add($"一套表{strYearPre}{strSeasonPre}A406");
            modifyTables.Add($"一套表{strYearYOY}{strSeasonYOY}A406");


            bool isAnyTableChanged = false;
            foreach (string OneTableName in modifyTables)
            {
                bool isTableExists = modifyOperator.CheckIfTableExists(OneTableName);
                if (!isTableExists)
                {
                    FuncOutCome modifyOutcome = new FuncOutCome();
                    modifyOutcome.FuncOutComeInfoArgs = $"{OneTableName}不存在,请先导入";
                    modifyOutcome.FuncSuccessCode = FuncOutComeCodeType.NullError;
                    modifyOutcome_List.Add(modifyOutcome);
                    //processedFiles++;
                    //percentage = (int)((double)processedFiles / totalFiles * 100);
                    //progress.Report(percentage);
                    continue;
                }
                else
                {
                    using (DataTable dt = modifyOperator.GetDataTable($"select * from {OneTableName}"))
                    {
                        string[] allColumnsNames = modifyOperator.GetColumnNamesArr(dt);
                        if (allColumnsNames.Contains("已修正"))
                        {
                            //processedFiles++;
                            //percentage = (int)((double)processedFiles / totalFiles * 100);
                            //progress.Report(percentage);
                            continue;
                        }
                        else
                        {
                            List<FuncOutCome> funcOutComes = modifyCityCountryTown(OneTableName);
                            if (funcOutComes.Count > 0)
                            { modifyOutcome_List.AddRange(funcOutComes); }
                            isAnyTableChanged = true;
                            //processedFiles++;
                            //percentage = (int)((double)processedFiles / totalFiles * 100);
                            //progress.Report(percentage);
                        }
                    }

                }
            }
            if (isAnyTableChanged) { WeakReferenceMessenger.Default.Send<MyMessage>(new MyMessage("已修正")); }
            return modifyOutcome_List;

        }
        /// <summary>
        /// 还是一次性根据养殖户编码,把对应的市\县\乡全部改成统计区划代码中的名称得了
        /// </summary>
        /// <param name="withYearAndSeasonTableName"></param>
        private static List<FuncOutCome> modifyCityCountryTown(string withYearAndSeasonTableName)
        {
            List<FuncOutCome> modifyOutcome_List = new List<FuncOutCome>();
            //MyDBOperator modifyOperator = new MyDBOperator();改用单例模式
            MyDBOperator modifyOperator = MyDBOperator.DBOperatorInstance;
            #region 改市的
            string sqlForGetAllCity = $"select 市代码 from {withYearAndSeasonTableName}  group by 市代码";//从某张季度表里面找到有几个市代码
            DataTable dtOfCityCodeAndName = modifyOperator.GetDataTable(sqlForGetAllCity);
            if (dtOfCityCodeAndName == null)//这种情况不太会出现,因为导入的时候全部设置了市代码\县代码\乡镇代码
            {
                modifyOutcome_List.Add(new FuncOutCome($"{withYearAndSeasonTableName}里面没有市代码"));
                return modifyOutcome_List;
            }
            else//如果市代码存在
            {
                List<string> strOfCityCodes_List = dtOfCityCodeAndName.AsEnumerable().
                    Select(r => r["市代码"].ToString())
                    .ToList();//获取市代码的的值(一般一个地级市就1个)
                if (strOfCityCodes_List.Count > 0)
                {
                    foreach (string itemCityCode in strOfCityCodes_List)//一般就1个
                    {
                        DataTable dtofRealNameOfitem = modifyOperator.GetDataTable($"select * from 全省区划代码 where 乡镇区划代码 ='{itemCityCode}'");
                        if (dtofRealNameOfitem == null)
                        {
                            FuncOutCome modifyOutcome = new FuncOutCome();
                            modifyOutcome.FuncOutComeInfoArgs = ($"2022年的全省统计区划代码没有市代码为{itemCityCode}的市,请排查{withYearAndSeasonTableName}表中户编码以此开头的养殖户");
                            modifyOutcome_List.Add(modifyOutcome);
                        }
                        else
                        {
                            string CorrectNameOfCityCode = dtofRealNameOfitem.Rows[0][0].ToString();
                            modifyOperator.ExecuteBySql($"update {withYearAndSeasonTableName} set 市 = '{CorrectNameOfCityCode}' where 市代码 = '{itemCityCode}'");
                        }
                    }
                }
            }
            #endregion

            #region 改乡镇的
            string SqlForGetAllTown = $"select 乡镇代码 from {withYearAndSeasonTableName} group by 乡镇代码";
            DataTable DTOfTownCodeAndName = modifyOperator.GetDataTable(SqlForGetAllTown);
            if (DTOfTownCodeAndName.Rows.Count > 0)
            {
                //获取要导入的表里的所有不同乡镇的乡镇代码
                List<string> ListStrOfTownCode = (from Row in DTOfTownCodeAndName.AsEnumerable()
                                                  select Row["乡镇代码"].ToString()).ToList();
                string 省里没有的乡镇代码 = null;
                foreach (string TownCodeItem in ListStrOfTownCode)
                {
                    DataTable DtOFOneTownInProvince = modifyOperator.GetDataTable($"select * from 全省区划代码 where 乡镇区划代码 = '{TownCodeItem}'");
                    if (DtOFOneTownInProvince.Rows.Count == 0)
                    {
                        FuncOutCome modifyOutcome = new FuncOutCome();
                        modifyOutcome.FuncOutComeInfoArgs = $"2022年的全省统计区划代码没有乡镇代码为{TownCodeItem}的乡镇,请排查{withYearAndSeasonTableName}表中户编码以此开头的养殖户";
                    }
                    else
                    {
                        string CorrectTownName = DtOFOneTownInProvince.Rows[0][0].ToString();
                        modifyOperator.ExecuteBySql($"update {withYearAndSeasonTableName} set 乡= '{CorrectTownName}' where 乡镇代码  = '{TownCodeItem}' ");
                    }
                }
            }
            #endregion

            #region 改县的
            string sqlToGetAllCountry = $"select 县代码 from {withYearAndSeasonTableName} group by 县代码";
            DataTable DTOfCountryCodeAndName = modifyOperator.GetDataTable(sqlToGetAllCountry);
            if (DTOfCountryCodeAndName.Rows.Count > 0)
            {
                //获取的是要导入的表里的所有县代码的集合
                List<string> ListStrOfCountryCode = DTOfCountryCodeAndName.AsEnumerable().Select(r => r["县代码"].ToString()).ToList();//从要修正的表中找到所有县代码,并组成集合
                string 省里没有的县代码 = null;
                foreach (string CountryCodeItem in ListStrOfCountryCode)
                {
                    DataTable DtOfOneCountryCodeAndNameInProvince = modifyOperator.GetDataTable($"select * from 全省区划代码 where 乡镇区划代码  = '{CountryCodeItem}'");
                    if (DtOfOneCountryCodeAndNameInProvince.Rows.Count == 0)
                    {
                        //MessageBox.Show($"2022年的全省统计区划代码没有县代码为{CountryCodeItem}的县区,请排查{WithYearAndSeasonTableName}表中户编码以此开头的养殖户", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        FuncOutCome modifyOutcome = new FuncOutCome();
                        modifyOutcome.FuncOutComeInfoArgs = $"2022年的全省统计区划代码没有县代码为{CountryCodeItem}的县区,请排查{withYearAndSeasonTableName}表中户编码以此开头的养殖户";
                    }
                    else
                    {
                        string CorrectCountryName = DtOfOneCountryCodeAndNameInProvince.Rows[0][0].ToString();//这里去掉了{CountryCodeitem}后面的三个0

                        // adoForCorrection.ExcuteByRows($"select 县,县代码 from {WithYearAndSeasonTableName} where 县代码 = '{CountryCodeitem}'", $"update {WithYearAndSeasonTableName} set 县 = '{CorrectCountryName}' where 县代码 = '{CountryCodeitem}'");
                        modifyOperator.ExecuteBySql($"update {withYearAndSeasonTableName} set 县 = '{CorrectCountryName}' where 县代码 = '{CountryCodeItem}' ");
                    }

                }
            }
            #endregion
            string 是否修正标识 = $"ALTER TABLE {withYearAndSeasonTableName} ADD COLUMN 已修正 string";
            modifyOperator.ExecuteBySql(是否修正标识);
            return modifyOutcome_List;
        }
    }
}
